﻿using DapperExtensions.Mapper;
using System;
using System.Text;
using System.Collections.Generic;
using System.Reflection;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Data.OracleClient;
using Dapper;
using DapperExtensions;
using DapperExtensions.Sql;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Linq;
using Google.Protobuf.WellKnownTypes;

namespace System.Data
{
    public class SqlServerDbSession:DbSession {
        #region 初始化必须
        /// <summary>
        ///初始化，需要传入连接字符串
        /// </summary>
        /// <param name="conStr">连接字符串</param>
        /// <returns></returns>
        public SqlServerDbSession(string conStr) : base(conStr)
        {
            DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.SqlServerDialect();
            DbType = DatabaseType.SqlServer;
        }

        /// <summary>
        /// 创建连接对象。子类需要重写，根据自己的数据库类型实例化自己的数据库连接对象(支持IDbConnection接口)。
        /// Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server。
        /// </summary>
        /// <returns></returns>
        protected override IDbConnection CreateConnection()
        {
            IDbConnection connection = new SqlConnection(ConnectionString);// System.Data.SqlClient
            return connection;
        }
        #endregion

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// <param name="files">列</param>
        /// <param name="tableName">表</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public override IEnumerable<T> GetPageSql<T>(string files, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total)
        {
            int skip = 1;
            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize + 1;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
            sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", files, tableName, where, orderby, skip, pageIndex * pageSize);


            var conn = CreateConnectionAndOpen();
            try
            {
                var reader = conn.QueryMultiple(sb.ToString());
                total = reader.ReadFirst<int>();
                return reader.Read<T>();
            }
            finally
            {
                conn.CloseIfOpen();
            }
        }
        #region 数据结构
        public override bool CreateTable(Type type)
        {
            var flg = false;
            #region SQL
            string tableInfo = AttributeHelper.GetClassDescription(type);
            string tableName = GetDbTableName(type).ToLower();
            StringBuilder placeholders = new StringBuilder();
            List<string> properytNames = new List<string>();
            properytNames.Add("ID");
            properytNames.Add("OrderBy");
            properytNames.Add("AddTime");
            List<string> sqls = new List<string>();
            PropertyInfo[] infos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetField);
            if (infos != null && infos.Length > 0)
            {
                sqls.Add(ExtendedProperty(false, tableInfo, tableName));
                StringBuilder sqlStr = new StringBuilder();
                try
                {
                    sqlStr.AppendLine("if exists (select * from sysobjects where id = OBJECT_ID('[" + tableName + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE[" + tableName + "] ");
                    sqlStr.AppendLine("CREATE TABLE [" + tableName + "](");
                    sqlStr.AppendLine("[ID] NVarChar(200) PRIMARY KEY ,");
                    foreach (PropertyInfo field in infos)
                    {
                        if (!properytNames.Contains(field.Name))
                        {
                            properytNames.Add(field.Name);
                            var maxAttribute = AttributeHelper.Get<MaxAttribute>(field);
                            if (maxAttribute != null)
                            {
                                sqlStr.AppendLine("[" + field.Name + "] NVarChar(max) NULL default(''),");
                            }
                            else
                            {
                                sqlStr.AppendLine("[" + field.Name + "] " + SqlTypeString2SqlType(field.PropertyType.Name, field.Name.ToLower()));
                            }
                            //字段描述
                            string description = AttributeHelper.GetDescription(field);
                            if (!string.IsNullOrWhiteSpace(description))
                            {
                                string fieldName = field.Name;
                                string info = description;
                                string sql = ExtendedProperty(false, info, tableName, fieldName);
                                sqls.Add(sql);
                            }
                        }
                    }
                    sqlStr.AppendLine("[OrderBy] int default(0),");
                    sqlStr.AppendLine("[AddTime] DateTime NOT NULL default(getdate())");
                    sqlStr.AppendLine(")");
                    flg = ExecuteNonQuery(sqlStr.ToString()) > 0;
                    ExecuteSqlTran(sqls);
                }
                catch (Exception ex)
                {
                    Logger.LogError("CreateTable error sql :" + sqlStr.ToString(), ex);
                }
            }
            #endregion
            return flg;
        }
        public override bool AddTableFieldInfo(string tableName, string fieldName, string info)
        {
            string sql = ExtendedProperty(false, info, tableName, fieldName);
            return ExecuteNonQuery(sql) > 0;
        }
        public override bool CreateTable<T>(bool cover = true) where T : class
        {
            #region SQL
            string tableName = GetDbTableName<T>();
            var types = typeof(T).GetType();
            string placeholders = "";
            foreach (var item in types.GetProperties())
            {
                placeholders += "[" + item.Name + "] " + SqlTypeString2SqlType(item.PropertyType.Name, item.Name);
            }
            StringBuilder sqlStr = new StringBuilder();
            if (cover)
            {
                sqlStr.Append("if exists (select * from sysobjects where id = OBJECT_ID('[" + tableName + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE[" + tableName + "]");
            }
            sqlStr.Append("CREATE TABLE [" + tableName + "](");
            sqlStr.Append(placeholders.Substring(0, placeholders.Length - 1) + ")");
            #endregion
            return ExecuteNonQuery(sqlStr.ToString()) > 0;
        }
        public override bool DropTable<T>() where T : class
        {
            string tableName = GetDbTableName<T>();
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("if exists (select * from sysobjects where id = OBJECT_ID('[" + tableName + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE[" + tableName + "]");
            return ExecuteNonQuery(sqlStr.ToString()) > 0;
        }
        public override bool CleanAllTableData<T>() where T : class
        {
            string tableName = GetDbTableName<T>();
            return ExecuteNonQuery("truncate table [" + tableName + "]") > 0;
        }

        public override List<DataBaseModel> GetDataBases()
        {
            List<DataBaseModel> dbmList = new List<DataBaseModel>();
            var dt = ExecuteDataTable("select name from sysdatabases where name not in('master','model','msdb','tempdb') order by name;");
            foreach (DataRow row in dt.Rows)
            {
                string dbName = row["name"].ToString();
                DataBaseModel dbm = new DataBaseModel();
                dbm.DatabaseName = dbName;
                dbmList.Add(dbm);
            }
            return dbmList;
        }
        public override List<TableModel> GetTables()
        {
            string sql = @"DECLARE @TableInfo TABLE ( name VARCHAR(50) , sumrows VARCHAR(11) , reserved VARCHAR(50) , data VARCHAR(50) , index_size VARCHAR(50) , unused VARCHAR(50) , pk VARCHAR(50) )
DECLARE @TableName TABLE ( name VARCHAR(50) )
DECLARE @name VARCHAR(50)
DECLARE @pk VARCHAR(50)
INSERT INTO @TableName ( name ) SELECT o.name FROM sysobjects o , sysindexes i WHERE o.id = i.id AND o.Xtype = 'U' AND i.indid < 2 ORDER BY i.rows DESC , o.name
WHILE EXISTS ( SELECT 1 FROM @TableName ) BEGIN SELECT TOP 1 @name = name FROM @TableName DELETE @TableName WHERE name = @name DECLARE @objectid INT SET @objectid = OBJECT_ID(@name) SELECT @pk = COL_NAME(@objectid, colid) FROM sysobjects AS o INNER JOIN sysindexes AS i ON i.name = o.name INNER JOIN sysindexkeys AS k ON k.indid = i.indid WHERE o.xtype = 'PK' AND parent_obj = @objectid AND k.id = @objectid INSERT INTO @TableInfo ( name , sumrows , reserved , data , index_size , unused ) EXEC sys.sp_spaceused @name UPDATE @TableInfo SET pk = @pk WHERE name = @name END
SELECT F.name as name, F.reserved  as reserved, F.data as data, F.index_size as index_size, RTRIM(F.sumrows) AS sumrows , F.unused as unused, ISNULL(p.tdescription, f.name) AS tdescription , F.pk as pk
FROM @TableInfo F LEFT JOIN ( SELECT name = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END , tdescription = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '') ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID = D.ID AND D.XTYPE = 'U' AND D.NAME <> 'DTPROPERTIES' LEFT JOIN sys.extended_properties F ON D.ID = F.major_id WHERE a.COLORDER = 1 AND F.minor_id = 0 ) P ON F.name = p.name
ORDER BY f.name ";

            var list = new List<TableModel>();
            var dt = ExecuteDataTable(sql);
            foreach (DataRow row in dt.Rows)
            {
                string tableName = row["name"].ToString();
                var tm = new TableModel();
                tm.TableName = tableName;
                tm.TableNameRemark = row["tdescription"].ToString();
                tm.TablePK = row["pk"].ToString();
                tm.Reserved = row["reserved"].ToString();
                tm.Data = row["data"].ToString();
                tm.IndexSize = row["index_size"].ToString();
                tm.SumRows = row["sumrows"].ToString();
                tm.Unused = row["unused"].ToString();
                if (tm.Columns == null || tm.Columns.Count == 0) tm.Columns = GetTableColumns(tm.TableName);
                if (tm.Columns.Find(p => p.ColumnName == "ParentID") != null)
                {
                    tm.IsTree = true;
                }
                list.Add(tm);
            }
            return list;
        }
        public override List<TableColumn> GetTableColumns(string tableName = "")
        {
            string sql = @"SELECT d.name  AS 表名, isnull(f.value, '') AS 表说明, a.colorder AS 字段序号, 
a.name AS 字段名,ISNULL(g.[value], '') AS 字段说明, 
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 
CASE WHEN EXISTS(SELECT 1  FROM dbo.sysindexes si INNER JOIN 
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc 
ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN  dbo.sysobjects so 
ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')AS 精度, 
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, 
ISNULL(e.text, '') AS 默认值 FROM dbo.syscolumns a LEFT OUTER JOIN   
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND  
d.status >= 0 LEFT OUTER JOIN   dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties g 
ON a.id = g.major_id AND a.colid = g.minor_id AND  g.name = 'MS_Description' LEFT OUTER JOIN  
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description'  ";
            if (!string.IsNullOrWhiteSpace(tableName))
            {
                sql = sql + " where d.name='" + tableName + "'";
            }
            var list = new List<TableColumn>();
            var dt = ExecuteDataTable(sql);
            foreach (DataRow row in dt.Rows)
            {
                var tc = new TableColumn();
                tc.TableName = row["表名"].ToString();
                tc.ColumnName = row["字段名"].ToString();
                tc.ColumnRemark = row["字段说明"].ToString();
                tc.ColumnType = row["类型"].ToString();
                tc.DefaultValue = row["默认值"].ToString();
                tc.MaxLength = int.Parse(row["长度"].ToString());
                if (row["标识"].ToString().Length > 0)
                {
                    tc.IsIdentity = true;
                }
                if (row["主键"].ToString() == "√")
                {
                    tc.IsPrimaryKey = true;
                }
                if (row["允许空"].ToString() == "√")
                {
                    tc.IsNullable = true;
                }
                list.Add(tc);
            }
            return list;
        }
        public override bool TestDataGenerate(string tableName, int count = 20)
        {
            string sqlf = "insert into {0}({1}) values({2})";
            var columns = GetTableColumns(tableName);
            for (int i = 0; i < count; i++)
            {
                string strcol = "";
                string strcolVal = "";
                var dynamicParams = new DynamicParameters();
                foreach (var item in columns)
                {
                    strcol = strcol + item.ColumnName + ",";
                    strcolVal = strcolVal + "@" + item.ColumnName + ",";
                    dynamicParams.Add(item.ColumnName.ToLower(), GetRandomValue(item.ColumnName.ToLower(), item.ColumnType.ToLower()));
                }
                string sql = string.Format(sqlf, tableName, strcol.DelLastChar(), strcolVal.DelLastChar());
                DbFactory.DbHelper().ExecuteNonQuery(sql, dynamicParams);
            }
            return true;
        }
        #endregion

        #region Utility
        private string ExtendedProperty(bool addbyupdate, string describe, string tableName, string columnName = null)
        {
            string sql_propertyInfo = @"EXEC sys.{0} N'MS_Description',N'{1}',N'SCHEMA',N'dbo',N'TABLE',N'{2}',N'COLUMN',N'{3}'";//字段说明
            string sql_propertyInfo1 = @"EXEC sys.{0} N'MS_Description',N'{1}',N'SCHEMA',N'dbo',N'TABLE',N'{2}'";//表说明
            string sql = "";
            if (addbyupdate)
            {//更新
                if (columnName == null || columnName.Length == 0)
                {
                    sql = string.Format(sql_propertyInfo1, "sp_updateextendedproperty", describe, tableName);
                }
                else
                {
                    sql = string.Format(sql_propertyInfo, "sp_updateextendedproperty", describe, tableName, columnName);
                }
            }
            else
            {
                if (columnName == null || columnName.Length == 0)
                {
                    sql = string.Format(sql_propertyInfo1, "sp_addextendedproperty", describe, tableName);
                }
                else
                {
                    sql = string.Format(sql_propertyInfo, "sp_addextendedproperty", describe, tableName, columnName);
                }
            }
            return sql;
        }
        //DOTO:C#类型转SQL SERVER数据类型
        private string SqlTypeString2SqlType(string sqlTypeString, string field = "")
        {
            string dbType = "NVarChar(200) NULL default(''),";
            switch (sqlTypeString.ToLower())
            {
                case "int":
                case "int?":
                case "int32":
                    dbType = field == "id" ? "int PRIMARY KEY IDENTITY(1,1) NOT NULL," : "int default(0),";
                    break;
                case "bool":
                case "bool?":
                case "boolean":
                    dbType = "bit NULL default(0),";
                    break;
                case "double":
                    dbType = "decimal(18, 4) NULL default(0),";
                    break;
                case "float":
                    dbType = "float NULL default(0),";
                    break;
                case "string":
                case "varbinary":
                    dbType = field == "id" ? "NVarChar(200) PRIMARY KEY ," : "NVarChar(200) NULL default(''),";
                    break;
                case "date":
                case "time":
                case "datetime":
                case "datetime?":
                case "datetime2":
                case "smalldatetime":
                case "datetimeoffset":
                    dbType = field == "addtime" ? "DateTime NULL default(getdate()) ," : "DateTime NULL,";
                    break;
                case "guid":
                case "uniqueidentifier":
                    dbType = "UniqueIdentifier NULL,";
                    break;
                default:
                    dbType = "NVarChar(200) NULL default(''),";
                    break;
            }
            return dbType;
        }
        /// <summary>
        /// 根据不同的数据类型生成测试数据
        /// https://www.w3school.com.cn/sql/sql_datatypes.asp
        /// </summary>
        public static string GetRandomValue(string columnName, string columnType, int lenght = 10)
        {
            if (columnName == "id") return UUIDUtils.Fid;
            if (columnName == "parentid") return Guid.Empty.ToString();
            if (columnName == "tenantid") return string.Empty;

            if (columnName == "adduserid") return "admin";
            if (columnName == "addtime") return DateTime.Now.ToString6();

            if (columnName == "deleteuserid") return "admin";
            if (columnName == "deletetime") return DateTime.Now.ToString6();
            if (columnName == "isdelete") return "0";

            if (columnName == "edituserid") return "admin";
            if (columnName == "edittime") return DateTime.Now.ToString6();

            string result = string.Empty;
            switch (columnType)
            {
                case "tinyint":
                case "bit":
                case "binary":
                case "varbinary":
                case "image":
                    result = "0";
                    break;
                case "smallint":
                case "mediumint":
                case "int":
                case "bigint":
                    result = RandomHelper.GetRandomNum(2);
                    break;
                case "float":
                case "double":
                case "decimal":
                case "money":
                case "numeric":
                    result = RandomHelper.GetRandomNum(2);
                    break;
                case "tinytext":
                case "text":
                case "varchar":
                case "nvarchar":
                case "ntext":
                case "char":
                case "nchar":
                    result = RandomHelper.GetRandChinese(5);
                    break;
                case "mediumtext":
                case "longtext":
                    result = RandomHelper.GetRandChinese(10);
                    break;
                case "date":
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                case "time":
                case "datetimeoffset":
                    result = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    break;
                case "timestamp":
                    result = DateTime.Now.ToTimestamp().ToString();
                    break;
            }
            return result;
        }
        #endregion
    }
}
